package com.assessment.mapper;

import com.assessment.model.entity.AssessmentRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * 评分记录Mapper接口
 */
public interface AssessmentRecordMapper extends BaseMapper<AssessmentRecord> {

    /**
     * 查询关系的评分记录
     */
    @Select("SELECT r.*, i.name as item_name, i.category_id, c.name as category_name " +
            "FROM t_assessment_record r " +
            "INNER JOIN t_assessment_item i ON r.item_id = i.id " +
            "INNER JOIN t_assessment_category c ON i.category_id = c.id " +
            "WHERE r.relation_id = #{relationId} AND r.deleted = 0 " +
            "ORDER BY c.sort, i.sort")
    List<AssessmentRecord> findByRelationId(@Param("relationId") Integer relationId);

    /**
     * 查询教师在周期内的评分记录
     */
    @Select("SELECT r.*, i.name as item_name, i.category_id, c.name as category_name, " +
            "t.name as assessor_name " +
            "FROM t_assessment_record r " +
            "INNER JOIN t_assessment_item i ON r.item_id = i.id " +
            "INNER JOIN t_assessment_category c ON i.category_id = c.id " +
            "INNER JOIN t_teacher t ON r.assessor_id = t.id " +
            "WHERE r.assessee_id = #{assesseeId} AND r.period_id = #{periodId} AND r.deleted = 0 " +
            "ORDER BY t.department_id, t.name, c.sort, i.sort")
    List<AssessmentRecord> findTeacherRecords(@Param("assesseeId") Integer assesseeId, @Param("periodId") Integer periodId);

    /**
     * 计算教师在周期内的平均得分
     */
    @Select("SELECT AVG(score) FROM t_assessment_record " +
            "WHERE assessee_id = #{assesseeId} AND period_id = #{periodId} AND deleted = 0")
    BigDecimal calculateAvgScore(@Param("assesseeId") Integer assesseeId, @Param("periodId") Integer periodId);

    /**
     * 计算教师在周期内某类别的平均得分
     */
    @Select("SELECT AVG(r.score) FROM t_assessment_record r " +
            "INNER JOIN t_assessment_item i ON r.item_id = i.id " +
            "WHERE r.assessee_id = #{assesseeId} AND r.period_id = #{periodId} " +
            "AND i.category_id = #{categoryId} AND r.deleted = 0")
    BigDecimal calculateCategoryAvgScore(@Param("assesseeId") Integer assesseeId,
                                         @Param("periodId") Integer periodId,
                                         @Param("categoryId") Integer categoryId);
}
